package main

import (
	_ "github.com/go-sql-driver/mysql"
	"database/sql"
	"fmt"
)

const driver  = "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8"

func main() {
	insert()
	fmt.Println("###########")
	query()
	fmt.Println("###########")
	update()
	fmt.Println("###########")
	query()
	fmt.Println("###########")
	delete()
	fmt.Println("###########")
}
func delete() {
	db, err := sql.Open("mysql", driver)
	checkErr(err)
	stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
	checkErr(err)
	res, err := stmt.Exec(1)
	checkErr(err)
	num, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(num)
}
func update() {
	db, err := sql.Open("mysql", driver)
	checkErr(err)
	stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
	checkErr(err)
	res, err := stmt.Exec(21, 2, 1)
	checkErr(err)
	num, err := res.RowsAffected()
	checkErr(err)
	fmt.Println(num)
}

func query() {
	db, err := sql.Open("mysql", driver)
	checkErr(err)
	rows, err := db.Query("SELECT * FROM user")
	checkErr(err)
	for rows.Next() {
		var userId int
		var userName string
		var userAge int
		var userSex int
		rows.Columns()
		err = rows.Scan(&userId, &userName, &userAge, &userSex)
		checkErr(err)
		fmt.Println(userId)
		fmt.Println(userName)
		fmt.Println(userAge)
		fmt.Println(userSex)
	}
}

func insert() {
	db, err := sql.Open("mysql", driver)
	checkErr(err)
	stmt, err := db.Prepare(`INSERT user (user_id,user_name,user_age,user_sex) values (?,?,?,?)`)
	res, err := stmt.Exec(1, "tony", 20, 1)
	checkErr(err)
	id, err := res.LastInsertId()
	checkErr(err)
	fmt.Println(id)
}
func checkErr(err error) {
	if err != nil {
		fmt.Println("error:", err)
		return
	}
}
